package com.esaheki.flashcards.dao;

import java.sql.*;
import java.util.ArrayList;

import com.esaheki.flashcards.classes.*;

public class CategoryDAO extends Conexao {
	
	public CategoryDAO() throws ClassNotFoundException, SQLException {
		super();
	}
	
	public void insertCategory(Category cat, User user) throws SQLException {
		String sql = "INSERT INTO categories VALUES (NULL, ?, ?, ?)";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setString(1, cat.getName());
		stmt.setInt(2, user.getId());
		stmt.setDouble(3, cat.getAverageScore());
		stmt.executeUpdate();
	}
	
	public void updateCategory(Category cat) throws SQLException {
		String sql = "UPDATE categories SET name = ?, score = ? WHERE id = ?";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setString(1, cat.getName());
		stmt.setDouble(2, cat.getAverageScore());
		stmt.setInt(3, cat.getId());
		stmt.executeUpdate();
	}
	
	public void deleteCategory(Category cat) throws SQLException {
		String sql = "DELETE FROM cards WHERE category = ?";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, cat.getId());
		stmt.executeUpdate();
		
		sql = "DELETE FROM categories WHERE id = ?";
		stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, cat.getId());
		stmt.executeUpdate();
	}
	
	public ArrayList<Category> getCategoryList(User user) throws SQLException {
		ArrayList<Category> list = new ArrayList<Category>();
		
		String sql = "SELECT * FROM categories WHERE user = ? ORDER BY name";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, user.getId());
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()) {
			Category cat = new Category();
			cat.setName(rs.getString("name"));
			cat.setId(rs.getInt("id"));
			cat.setAverageScore(rs.getDouble("score"));
			list.add(cat);
		}
		return list;
	}

}
